package cemetery.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import cemetery.bean.materiel;
import cemetery.dao.BaseDao;
import cemetery.dao.MaterielDao;

public class MaterielDaoImpl extends BaseDao implements MaterielDao {

	@Override
	public List<materiel> getList(materiel news) {
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		List<materiel> list=new ArrayList<>();
		try {
			conn=getconnection();
			String sql=" select id,name,number,science,sourc,pprice,price,personincharge "
			+" from materiel where 1=1 ";
			if (news!=null && news.getId()!=0) {
				sql=sql+" and id='"+news.getId()+"' ";
			}
			if (news!=null && news.getName()!=null&& !"".equals(news.getName())) {
				sql=sql+" and name like '%"+news.getName()+"%' ";
			}
			stmt=conn.prepareStatement(sql);
			rs=stmt.executeQuery();
			while (rs.next()) {
				materiel n=new materiel();
				n.setId(rs.getInt(1));
				n.setName(rs.getString(2));
				n.setNumber(rs.getInt(3));
				n.setScience(rs.getString(4));
				n.setSourc(rs.getString(5));
				n.setPprice(rs.getInt(6));
				n.setPrice(rs.getInt(7));
				n.setPersonincharge(rs.getString(8));
				list.add(n);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			this.closeAll(conn, stmt, rs);
		}
		return list;
	}

	@Override
	public int add(materiel news) {
		String psql="insert into materiel(name,number,science,sourc,pprice,price,personincharge)values(?,?,?,?,?,?,?)";
		Object[]params= {news.getName(),news.getNumber(),news.getScience(),news.getSourc(),news.getPprice(),news.getPrice(),news.getPersonincharge()};
		return this.executeUpdate(psql, params);
	}

	@Override
	public int update(materiel news) {
		String psql="update  materiel set name=?,number=?,science=?,sourc=?,pprice=?,price=?,personincharge=? where id=?";
		Object[]params= {news.getName(),news.getNumber(),news.getScience(),news.getSourc(),news.getPprice(),news.getPrice(),news.getPersonincharge()};
		return this.executeUpdate(psql, params);
	}

	@Override
	public int deleteById(int id) {
		String psql="delete from materiel where id=?";
		Object[]params= {id};
		return this.executeUpdate(psql, params);
	}

	@Override
	public int getTotalCount(materiel news) {
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		int totalCount=0;
		try {
			conn=getconnection();
			String sql="select count(1) from materiel where 1=1 ";
			if (news!=null && news.getId()!=0) {
				sql=sql+" and id='"+news.getId()+"' ";
			}
			if (news!=null && news.getName()!=null&& !"".equals(news.getName())) {
				sql=sql+" and name like '%"+news.getName()+"%' ";
			}
			stmt=conn.prepareStatement(sql);
			rs=stmt.executeQuery();
			while (rs.next()) {
				totalCount=rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			this.closeAll(conn, stmt, rs);
		}
		return totalCount;
	}

	@Override
	public List<materiel> setPageList(materiel news, int pageIndex, int pageSize) {
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		List<materiel> list=new ArrayList<>();
		try {
			conn=getconnection();
			String sql="select id,name,number,science,sourc,pprice,price,personincharge"
			+" from materiel where 1=1 ";
			if (news!=null && news.getId()!=0) {
				sql=sql+" and nid='"+news.getId()+"' ";
			}
			if (news!=null && news.getName()!=null&& !"".equals(news.getName())) {
				sql=sql+" and new_title like '%"+news.getName()+"%' ";
			}
			sql=sql+" limit "+(pageIndex-1)*pageSize+","+pageSize;
			stmt=conn.prepareStatement(sql);
			rs=stmt.executeQuery();
			while (rs.next()) {
				materiel n=new materiel();
				n.setId(rs.getInt(1));
				n.setName(rs.getString(2));
				n.setNumber(rs.getInt(3));
				n.setScience(rs.getString(4));
				n.setSourc(rs.getString(5));
				n.setPprice(rs.getInt(6));
				n.setPrice(rs.getInt(7));
				n.setPersonincharge(rs.getString(8));
				list.add(n);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			this.closeAll(conn, stmt, rs);
		}
		return list;
	}

}
